insert overwrite table jms_dm.dm_customer_reback_net_detail_dt partition(dt)
select waybill_no
     ,date_format(apply_time, 'yyyy-MM-dd') as date_time
     ,sender_network_name
     ,sender_network_code
     ,customer_code
     ,customer_name
     ,apply_type_name
     ,date_format(apply_time, 'yyyy-MM-dd')
from jms_dwd.dwd_tab_reback_transfer_express_base
where date_format(apply_time, 'yyyy-MM-dd') = '{{ execution_date | cst_ds }}'
  and apply_type_code =4
    distribute by 1;

insert overwrite table jms_dm.dm_customer_reback_net_sum_dt partition(dt)
select date_time
       ,sender_network_name
       ,sender_network_code
       ,customer_code
       ,customer_name
       ,count(waybill_no)
       ,dt
  from jms_dm.dm_customer_reback_net_detail_dt
 where dt= '{{ execution_date | cst_ds }}'
 group by date_time
       ,sender_network_name
       ,sender_network_code
       ,customer_code
       ,customer_name
       ,dt
 distribute by dt;

insert overwrite table jms_dm.dm_customer_worn_net_sum_dt partition(dt)
select date_time
      ,pick_network_name
      ,pick_network_code
      ,customer_code
      ,customer_name
      ,count(waybill_no)
      ,dt
 from jms_dm.dm_sqs_worn_waybill_detail_mid_dt
where dt>= date_add('{{ execution_date | cst_ds }}', -90) and dt<=date_add('{{ execution_date | cst_ds }}', 0)
 group by date_time
       ,pick_network_name
       ,pick_network_code
       ,customer_code
       ,customer_name
       ,dt
 distribute by dt;

insert overwrite table jms_dm.dm_customer_loss_net_sum_dt partition(dt)
select date_time
      ,pick_network_name
      ,pick_network_code
      ,customer_code
      ,customer_name
      ,count(waybill_no)
      ,dt
 from jms_dm.dm_sqs_loss_detail_dt
where dt>= date_add('{{ execution_date | cst_ds }}', -31) and dt<=date_add('{{ execution_date | cst_ds }}', 0) 
  and loss_type ='遗失' and is_filter = 1 
group by date_time
      ,pick_network_name
      ,pick_network_code
      ,customer_code
      ,customer_name
      ,dt
 distribute by dt;

 

